package com.cyou.qb.common.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.cyou.nad.datastore.DataList;
import com.cyou.nad.datastore.query.JdbcPage;
import com.cyou.nad.datastore.query.JdbcPageDao;
import com.cyou.qb.common.dao.StatActiveDayDao;
import com.cyou.qb.common.domain.QiangbaAdmin;
import com.cyou.qb.common.domain.StatActiveDay;
import com.cyou.qb.common.service.StatisticsService;
import com.cyou.qb.exception.ServiceException;

@Service("StatisticsServiceImpl")
public class StatisticsServiceImpl extends BaseSerivceImpl implements StatisticsService{

	@Resource(name="jdbcPageDao")
	private JdbcPageDao jdbcPageDao;
	@Autowired
	private StatActiveDayDao statisticsDao;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	
	private String createActivityStateSql(int activityState, String sql) {
		if (activityState == 1) {//尚未开始。只要未发布，开启即可
			sql += "\n and (active.publish = 0 or active.start_time>now()) and active.status=1";
		} else if (activityState == 2) {//正在进行 在时间中并且发布，开启
			sql += "\n and active.start_time <= now() and active.end_time >= now() and active.publish=1 and active.status=1";
		} else if (activityState == 3) {//已经结束 时间超过，发布，开启
			sql += " and active.end_time < now() and active.publish = 1 and active.status=1";
		} else if (activityState == 4) {//关闭
			sql += " and active.status=2";
		}
		return sql;
	}

	/**
	 * 判断管理员类型
	 * @param admin
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private String adminType(QiangbaAdmin admin, String sql, List<Object> params) throws Exception {
		//判断管理员类型
		if(admin.getStatus()==1 && admin.getValid()==1){
			if(admin.getType().equals("1")){
				sql += "\n and active.admin_id = ? ";
				params.add(admin.getAdminId());
			}
		}else{
			throw new ServiceException("该用户无权查看");
		}
		return sql;
	}

	@Override
	public JdbcPage findPageQueryStatistics(QiangbaAdmin admin, int pageNo, int pageSize, String activityName, int activityState, Date startDate, Date endDate) throws Exception {
		//查询sql
		String sql = "\n select stat.active_id as activeId, active.name as activeName, sum(stat.join_num) as joinNum, sum(stat.take_num) takeNum, sum(stat.g_in) gIn, sum(stat.g_out) as gOut,"
					+ "\n case WHEN ((active.publish = 0 or active.start_time > ?) and active.status=1 ) THEN 1 "
					+ "\n WHEN active.status=2 THEN 4"
					+ "\n WHEN (active.start_time <= ? and active.end_time >= ? and active.publish=1 and active.status=1) THEN 2"
					+ "\n WHEN (active.end_time < ? and active.publish = 1 and active.status=1) THEN 3"
					+ "\n else -1 " 
					+ "\n end as faceStatus"
					+ "\n from t_stat_active_day as stat,t_active as active,t_sys_admin as admin"
					+ "\n where stat.active_id=active.id and active.admin_id=admin.id";
		
		//查询条件
		List<Object> params = new ArrayList<Object>();
		Date now = new Date();
		params.add(now);
		params.add(now);
		params.add(now);
		params.add(now);
		
		sql = adminType(admin, sql, params);
		
		//活动名称查询
		if (activityName != null){
			sql += "\n and active.name like ?";
			params.add("%"+activityName+"%");
		} else if (activityState != 0){//活动名称与活动状态二选一
			sql = createActivityStateSql(activityState, sql);
		}
		//起止时间
		if (startDate != null){
			sql += "\n and stat.date>=?";
			params.add(startDate);
		}
		if (endDate != null) {
			sql += "\n and stat.date<=?";
			params.add(endDate);
		}
		sql += "\n group by stat.active_id order by active.create_date desc";
		return jdbcPageDao.pageQuery(sql, pageNo, pageSize, params.toArray());
	}

	@Override
	public DataList findDetailStatistics(String activityId, int activityState, Date startDate, Date endDate) {
		//查询语句
		String sql = "\n select stat.date as date, stat.join_num as joinNum, stat.take_num as takeNum, stat.g_in as gIn, stat.g_out as gOut"
					+ "\n from t_stat_active_day as stat LEFT JOIN t_active as active"
					+ "\n on stat.active_id=active.id where 1=1"
					+ "\n and stat.active_id=?";
		List<Object> params = new ArrayList<Object>();
		params.add(activityId);
		//设置活动状态
		if (activityState != 0){
			sql = createActivityStateSql(activityState, sql);
		}
		//起止时间
		if (startDate != null){
			sql += "\n and stat.date>=?";
			params.add(startDate);
		}
		if (endDate != null) {
			sql += "\n and stat.date<=?";
			params.add(endDate);
		}
		sql += "\n order by stat.date";
		return jdbcPageDao.query(sql, params.toArray());
	}

	@Override
	public DataList findTotalStatistics(QiangbaAdmin admin, int activityState, Date startDate, Date endDate) throws Exception {
//		String sql = "\nselect stat.date as date, sum(stat.join_num) as joinNum, sum(stat.take_num) as takeNum, sum(stat.g_in) as gIn, sum(stat.g_out) as gOut"
//				+"\nfrom t_stat_active_day as stat where 1=1";
		String sql = "\n select stat.date as date, sum(stat.join_num) as joinNum, sum(stat.take_num) as takeNum, sum(stat.g_in) as gIn, sum(stat.g_out) as gOut"
				+ "\n from t_stat_active_day as stat,t_active as active,t_sys_admin admin"
				+ "\n where stat.active_id=active.id and active.admin_id=admin.id";
		List<Object> params = new ArrayList<Object>();
		
		sql = adminType(admin, sql, params);
		
 		if (activityState != 0){//非全部查询
			sql = createActivityStateSql(activityState, sql);
		}
 		if (startDate != null) {
 			sql += "\n and stat.date>=?";
 			params.add(startDate);
 		}
 		if (endDate != null) {
 			sql += "\n and stat.date<=?";
 			params.add(endDate);
 		}
 		sql += "\n group by stat.date";
		return jdbcPageDao.query(sql, params.toArray());
	}

	@Override
	public DataList findQueryTotal(QiangbaAdmin admin, String searchType, String activityId, String activityName, int activityState, Date startDate,
			Date endDate) throws Exception {
		String sql = "\n select sum(stat.join_num) as joinTotalNum, sum(stat.take_num) as takeTotalNum, sum(stat.g_in) as gTotalIn, sum(stat.g_out) as gTotalOut"
				+ "\n from t_stat_active_day as stat, t_active as active, t_sys_admin as admin"
				+ "\n where stat.active_id=active.id and active.admin_id=admin.id";
		List<Object> params = new ArrayList<Object>();
		
		//根据管理员类型修改sql
		sql = adminType(admin, sql, params);
		
		//开始结束时间
		//起止时间
		if (startDate != null){
			sql += "\n and stat.date>=?";
			params.add(startDate);
		}
		if (endDate != null) {
			sql += "\n and stat.date<=?";
			params.add(endDate);
		}
		
		if (searchType == null){//查询全部
			//活动名称查询
			if (activityName != null){
				sql += "\n and active.name like ?";
				params.add("%"+activityName+"%");
			} else if (activityState != 0){//活动名称与活动状态二选一
				sql = createActivityStateSql(activityState, sql);
			}
		}else if ("detail".equals(searchType)) {//详细页的合计统计
			//查询语句
			sql += "\n and stat.active_id=?";
			params.add(activityId);
			//设置活动状态
//			if (activityState != 0){
//				sql = createActivityStateSql(activityState, sql);
//			}
		}else if ("total".equals(searchType)){
			//活动状态
			if (activityState != 0){
				sql = createActivityStateSql(activityState, sql);
			}
		}
		return jdbcPageDao.query(sql,params.toArray());
	}

	@Override
	public int saveOrUpdateOneStatistics(StatActiveDay statActiveDay) {
		//先查询
		int count = statisticsDao.updateOneStatActiveDay(statActiveDay);
		if (count == 0){//更新0条记录，不存在相关记录，直接插入一条
			count = statisticsDao.saveOneStatActiveDay(statActiveDay);
		}
		return count;
	}
	

	@Override
	public int findActiveGOut(int activeId, Date start, Date end) {
		String sql = "\nselect sum(t_award.g_num)"
					+ "\n from t_winner LEFT JOIN t_award"
					+ "\n on t_winner.award_id=t_award.id and t_winner.active_id=t_award.active_id"
					+ "\n where t_award.type='3'"
					+ "\n and t_award.active_id=?"
					+ "\n and t_winner.win_time>=?"
					+ "\n and t_winner.win_time<?";//取G币类型求和
		List<Object> params = new ArrayList<Object>();
		params.add(activeId);
		params.add(start);
		params.add(end);
		return jdbcTemplate.queryForInt(sql, params.toArray());
//		DataList dataList = jdbcPageDao.query(sql, params.toArray());
//		return dataList.getInt(0, 0);
		//return this.statisticsDao.findActiveGout(activeId, start, end);
	}

}
